package MypageController.MyKategore;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import Connect.connector;
import MypageController.MyWrite.MyWriteBean;

public class MyKategoreDAO{
	
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public MyWriteBean[] kategoreEntriesGet(int mypageid, String kategore, int number, int size){
		conn = connector.getConnection();
		ArrayList<MyWriteBean> result = new ArrayList<MyWriteBean>();
		int startnum;
		try{
			
			if(number == 1){
				startnum = 0;
			}else{
				startnum = (number-1)*size;
			}
			
			String sql = "select * from (select * from ENTRIES where Blogs_Id = ? && Ent_Kategorie = ? ORDER BY Ent_DateString DESC) a where a.Ent_DateString limit ?, ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageid);
			pstmt.setString(2, kategore);
			pstmt.setInt(3, startnum);
			pstmt.setInt(4, size);
			
			rs = pstmt.executeQuery();
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			while( rs.next()){
				MyWriteBean mwbean = new MyWriteBean();
				mwbean.setID(rs.getInt("ID"));
				mwbean.setEnt_Title(rs.getString("Ent_Title"));
				mwbean.setEnt_Contents(rs.getString("Ent_Contents"));
				mwbean.setEnt_Nick(rs.getString("Ent_Nick"));
				mwbean.setEnt_Kategorie(rs.getString("Ent_Kategorie"));
				mwbean.setEnt_DateString(sdf.format(rs.getTimestamp("Ent_DateString")));
				mwbean.setEnt_Date(rs.getDate("Ent_Date"));
				mwbean.setEnt_ComCount(rs.getInt("Ent_ComCount"));
				mwbean.setEnt_Number(rs.getInt("Ent_Number"));
				result.add(mwbean);
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new MyWriteBean[0]);
	}
	
	public MyWriteBean[] kateDateEntriesGet(int mypageid, Date date, int number, int size){
		conn = connector.getConnection();
		ArrayList<MyWriteBean> result = new ArrayList<MyWriteBean>();
		int startnum;
		try{
		
			if(number == 1){
				startnum = 0;
			}else{
				startnum = (number-1)*size;
			}
			
			String sql = "select * from (select * from ENTRIES where Blogs_Id = ? && Ent_Date = ? ORDER BY Ent_DateString DESC) a where a.Ent_DateString limit ?, ?";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageid);
			pstmt.setDate(2, date);
			pstmt.setInt(3, startnum);
			pstmt.setInt(4, size);
			
			rs = pstmt.executeQuery();
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			while( rs.next()){
				MyWriteBean mwbean = new MyWriteBean();
				mwbean.setID(rs.getInt("ID"));
				mwbean.setEnt_Title(rs.getString("Ent_Title"));
				mwbean.setEnt_Contents(rs.getString("Ent_Contents"));
				mwbean.setEnt_Nick(rs.getString("Ent_Nick"));
				mwbean.setEnt_Kategorie(rs.getString("Ent_Kategorie"));
				mwbean.setEnt_DateString(sdf.format(rs.getTimestamp("Ent_DateString")));
				mwbean.setEnt_Date(rs.getDate("Ent_Date"));
				mwbean.setEnt_ComCount(rs.getInt("Ent_ComCount"));
				mwbean.setEnt_Number(rs.getInt("Ent_Number"));
				result.add(mwbean);
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new MyWriteBean[0]);
	}
	
	public int kategoreEntriesCount(int mypageid, String kategore){
		conn = connector.getConnection();
		int count = 0;
		try{
			
			String sql = "select count(*) from ENTRIES where Blogs_Id = ? && Ent_Kategorie = ? ORDER BY Ent_DateString DESC";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageid);
			pstmt.setString(2, kategore);

			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				count = rs.getInt("count(*)");
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return count;
	}
	
	public int kateDateEntriesCount(int mypageid, Date date){
		conn = connector.getConnection();
		int count = 0;
		try{
			
			String sql = "select count(*) from ENTRIES where Blogs_Id = ? && Ent_Date = ? ORDER BY Ent_DateString DESC";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageid);
			pstmt.setDate(2, date);

			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				count = rs.getInt("count(*)");
			}
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return count;
	}
	
	public void kategoreDelet(int mypageid, String kategore){
		conn = connector.getConnection();
		try{
			
			String sql = "DELETE FROM `KATEGORIES` WHERE `Blogs_Id`=? && `Kate_Title`=?";		
			pstmt = conn.prepareStatement( sql );			
			pstmt.setInt(1, mypageid);	
			pstmt.setString(2, kategore);
			pstmt.executeUpdate();	
			
			sql = "DELETE FROM `ENTRIES` WHERE `Blogs_Id`=? && `Ent_kategorie`=?";			
			pstmt = conn.prepareStatement( sql );			
			pstmt.setInt(1, mypageid);	
			pstmt.setString(2, kategore);			
			pstmt.executeUpdate();	
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public void kategoreAdd(int mypageid, String kategore){
		conn = connector.getConnection();
		try{
			
			String sql = "INSERT INTO KATEGORIES (`Blogs_Id`, `Kate_Title`) VALUES (?, ?)";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageid);	
			pstmt.setString(2, kategore);
			
			pstmt.executeUpdate();	
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public Integer[] kategoreEntriesIdGet(int mypageid, String kategore){
		conn = connector.getConnection();
		ArrayList<Integer> list = new ArrayList<Integer>();
		try{
			
			String sql = "select ID from ENTRIES where Blogs_Id = ? && Ent_Kategorie = ? ORDER BY `Ent_DateString` DESC";
			
			pstmt = conn.prepareStatement( sql );
			
			pstmt.setInt(1, mypageid);
			pstmt.setString(2, kategore);
			
			rs = pstmt.executeQuery();
			
			while( rs.next()){
					
				list.add(Integer.parseInt(rs.getString("ID")));
					
			}
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return list.toArray(new Integer[0]);
	}
	
	public void commentDelet(int mypageid, int entriesid){
		conn = connector.getConnection();
		try{
			String sql = "DELETE FROM `COMMENTBLOGS` WHERE Blogs_Id=? && Entries_Id=?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setInt(1, mypageid);	
			pstmt.setInt(2, entriesid);
			pstmt.executeUpdate();
			
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
}
